Monthly Cash Budget Under Sales and Collections Uncertainty

This paper develops a monthly cash budget modeled in Excel to illustrate how a spreadsheet simulation analysis can be performed to directly incorporate uncertainty in both sales and collection patterns to determine cash balances. The paper provides a numerical example, formulas as well as assumptions for a cash budget extending from January through December. It also describes how to perform a simulation using the build-in capabilities of Excel RAND function. The cash budget model is run 5000 times to capture the probability distribution of the minimum ending cash balance over that period (end of July). Simulation results support that the mean cash balance in July is considerably less than the positive cash balance obtained in July when no uncertainty in sales and collection patterns are assumed and that the standard deviation of the July cash balance is quite large. In fact, results indicate that there is a 50 percent probability that the July cash balance will be negative. Cash budgeting decisionmaking is clearly improved by specifying normal probability distributions for each uncertain critical variable such as sales and collection patterns. Excel’s build-in capabilities allow students with no programming skills to perform complex and realistic risk analyses of real financial problems.